﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using org.in2bits.MyXls;

namespace Wicresoft.SMS.Common
{
    public class ExcelMyXls
    {
        /// <summary>
        /// 保存xls到当前目录并返回文件名
        /// </summary>
        /// <param name="array"></param>
        /// <returns></returns>
        public string XlsWrite(string[,] array, string path)
        {
            XlsDocument xls = new XlsDocument();
            xls.FileName = Convert.ToString(DateTime.Now.Ticks);
            Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");
            xls.DocumentSummaryInformation.Company = "Alcatel--Lucent";//填加文件公司信息 
            xls.SummaryInformation.Author = "smsGateway";//作者信息

            XF cellXF = xls.NewXF();//title的cell格式
            XF cellXS = xls.NewXF();//内容的cell格式
            cellXF.HorizontalAlignment = HorizontalAlignments.Centered;//设定文字居中 
            cellXF.Font.FontName = "宋体";//设定字体 
            cellXF.Font.Height = 20 * 20;//设定字大小（字体大小是以 1/20 point 为单位的） 
            cellXF.Font.Bold = true;
            cellXF.PatternBackgroundColor = Colors.Grey;
            cellXS.HorizontalAlignment = HorizontalAlignments.Centered;

            Cell cell;
            cell = sheet.Cells.Add(1, 1, "Group", cellXF);//以设定好的格式填加cell 
            cell = sheet.Cells.Add(1, 2, "Name", cellXF);//以设定好的格式填加cell 
            cell = sheet.Cells.Add(1, 3, "Number", cellXF);//以设定好的格式填加cell 

            for (int i = 0; i < array.GetLength(0); i++)
                for (int j = 0; j < array.GetLength(1); j++)
                {
                    cell = sheet.Cells.Add(i + 2, j + 1, array[i, j], cellXS);

                }
            string dir = path + "Content/Excels";

            if (!Directory.Exists(dir))
                Directory.CreateDirectory(dir);
            xls.Save(dir);

            return xls.FileName;
        }

        XlsDocument book;

        public ExcelMyXls()
        {

        }

        public ExcelMyXls(Stream stream)
        {
            this.book = new XlsDocument(stream);
        }

        private DataTable ToDataTable(int sheetindex)
        {
            Worksheet ws = this.book.Workbook.Worksheets[sheetindex];
            return this.SavaAsDataTable(ws);
        }

        private DataTable SavaAsDataTable(Worksheet sheet)
        {
            DataTable dt = new DataTable();
            if (sheet == null)
                return dt;
            if (sheet.Rows.Count > 0)
            {
                Row firstrow = sheet.Rows[Convert.ToUInt16(sheet.Rows.MinRow)];
                for (ushort i = 1; i <= firstrow.CellCount; i++)
                {
                    dt.Columns.Add(firstrow.GetCell(i).Value.ToString().ToLower());
                }

                for (uint i = sheet.Rows.MinRow + 1; i <= sheet.Rows.MaxRow; i++)
                {
                    Row row = sheet.Rows[Convert.ToUInt16(i)];                    
                    if (row != null)
                    {
                        DataRow dr = dt.NewRow();
                        for (ushort j = row.MinCellCol; j <= row.MaxCellCol; j++)
                        {
                            Cell cell = row.GetCell(j);
                            if (cell != null)
                            {
                                dr[Convert.ToInt32(j) - 1] = cell.Value != null ? cell.Value.ToString().Trim() : string.Empty;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

        public static DataTable TranslateToTable(Stream stream, int index)
        {
            ExcelMyXls emx = new ExcelMyXls(stream);
            return emx.ToDataTable(index);
        }
    }
}
